using Infrastructure;
using Infrastructure.Attribute;
using SqlSugar;
using System.Text.RegularExpressions;
using System.Text;
using ZR.Common;
using ZR.Model;
using ZR.Model.System.Dto;
using ZR.ServiceCore.Services.IService;

namespace ZR.ServiceCore.Services
{
    /// <summary>
    /// 表查询服务实现
    /// </summary>
    [AppService(ServiceType = typeof(ITableQueryService), ServiceLifetime = LifeTime.Transient)]
    public class TableQueryService : BaseService<object>, ITableQueryService
    {

        /// <summary>
        /// 查询包含Z_RADR_I的表列表（优化版本：在数据库层面进行筛选和分页，带缓存）
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <param name="pager">分页参数</param>
        /// <returns></returns>
        public PagedInfo<TableQueryResultDto> SelectTableList(TableQueryDto query, PagerInfo pager)
        {
            try
            {
                // 生成缓存键（基于查询条件和分页参数）
                var cacheKey = BuildCacheKey(query, pager);
                
                // 尝试从缓存获取结果
                var cachedResult = CacheHelper.GetCache<PagedInfo<TableQueryResultDto>>(cacheKey);
                if (cachedResult != null)
                {
                    Console.WriteLine($"表查询 - 从缓存获取数据，缓存键: {cacheKey}");
                    return cachedResult;
                }

                // 缓存未命中，执行数据库查询
                // 构建优化的SQL查询，在数据库层面进行筛选、排序和分页
                var (countSql, dataSql) = BuildOptimizedQuerySql(query, pager);

                // 先查询总数（用于分页信息）
                var totalCount = Context.Ado.GetInt(countSql);
                
                // 查询分页数据（只查询当前页需要的数据）
                var tableData = Context.Ado.SqlQuery<dynamic>(dataSql).ToList();

                // 转换为结果对象
                var result = new List<TableQueryResultDto>();
                foreach (var item in tableData)
                {
                    string tableName = item.TABLE_NAME?.ToString() ?? "";
                    var dateTimeString = ExtractDateTimeFromTableName(tableName);
                    
                    if (!string.IsNullOrEmpty(dateTimeString))
                    {
                        var formattedDateTime = FormatDateTime(dateTimeString);
                        result.Add(new TableQueryResultDto
                        {
                            TableName = tableName,
                            DateTimeString = dateTimeString,
                            FormattedDateTime = formattedDateTime
                        });
                    }
                }

                var pageResult = new PagedInfo<TableQueryResultDto>
                {
                    TotalNum = totalCount,
                    PageSize = pager.PageSize,
                    PageIndex = pager.PageNum,
                    Result = result
                };

                // 将结果存入缓存（缓存10分钟）
                CacheHelper.SetCache(cacheKey, pageResult, 10);
                Console.WriteLine($"表查询 - 查询数据库并缓存结果，缓存键: {cacheKey}");

                return pageResult;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"表查询 - 查询出错: {ex.Message}");
                throw;
            }
        }

        /// <summary>
        /// 构建缓存键（基于查询条件和分页参数）
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <param name="pager">分页参数</param>
        /// <returns></returns>
        private string BuildCacheKey(TableQueryDto query, PagerInfo pager)
        {
            var keyBuilder = new StringBuilder("TableQuery:List:");
            
            // 查询类型
            keyBuilder.Append($"Type:{query.Type ?? "All"}:");
            
            // 城市参数
            keyBuilder.Append($"City:{query.City ?? "All"}:");
            
            // 开始时间
            if (query.BeginTime.HasValue)
            {
                keyBuilder.Append($"BeginTime:{query.BeginTime.Value:yyyyMMddHHmmss}:");
            }
            
            // 结束时间
            if (query.EndTime.HasValue)
            {
                keyBuilder.Append($"EndTime:{query.EndTime.Value:yyyyMMddHHmmss}:");
            }
            
            // 分页参数
            keyBuilder.Append($"PageNum:{pager.PageNum}:PageSize:{pager.PageSize}");
            
            return keyBuilder.ToString();
        }

        /// <summary>
        /// 构建优化的查询SQL（在数据库层面进行筛选、排序和分页）
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <param name="pager">分页参数</param>
        /// <returns>返回(计数SQL, 数据SQL)</returns>
        private (string countSql, string dataSql) BuildOptimizedQuerySql(TableQueryDto query, PagerInfo pager)
        {
            var whereConditions = new List<string>();
            
            // 基础条件：表类型和表名模式
            whereConditions.Add("TABLE_TYPE = 'BASE TABLE'");
            whereConditions.Add("TABLE_NAME LIKE '%_CDWL_%'");
            
            // 根据查询类型添加条件
            switch (query.Type?.ToUpper())
            {
                case "PPI":
                    whereConditions.Add("TABLE_NAME NOT LIKE '%LR_096%'");
                    whereConditions.Add("TABLE_NAME NOT LIKE '%PPI_LR%'");
                    break;
                case "DBS":
                    whereConditions.Add("TABLE_NAME LIKE '%LR_096%'");
                    break;
                case "PPILR":
                    whereConditions.Add("TABLE_NAME LIKE '%PPI_LR%'");
                    break;
            }
            
            // 城市筛选：在SQL层面进行
            if (!string.IsNullOrEmpty(query.City))
            {
                // 转义特殊字符，防止SQL注入
                var escapedCity = query.City.Replace("'", "''").Replace("_", "\\_").Replace("%", "\\%");
                whereConditions.Add($"TABLE_NAME LIKE '%_{escapedCity}_%'");
            }
            
            // 日期范围筛选：在SQL层面进行
            // 表名格式：20250605165800_P_CDWL_...，前14位是日期时间
            if (query.BeginTime.HasValue)
            {
                var beginDateStr = query.BeginTime.Value.ToString("yyyyMMddHHmmss");
                whereConditions.Add($"SUBSTRING(TABLE_NAME, 1, 14) >= '{beginDateStr}'");
            }
            
            if (query.EndTime.HasValue)
            {
                var endDateStr = query.EndTime.Value.ToString("yyyyMMddHHmmss");
                whereConditions.Add($"SUBSTRING(TABLE_NAME, 1, 14) <= '{endDateStr}'");
            }
            
            // 只查询包含14位数字开头的表名（有效日期格式）
            // 如果日期范围已设置，则不需要额外检查；否则检查前14位是否为数字
            if (!query.BeginTime.HasValue && !query.EndTime.HasValue)
            {
                // 如果没有日期范围，则只查询以14位数字开头的表名
                // 使用 CAST 尝试转换前14位为数字，如果成功则说明是数字
                whereConditions.Add("CAST(SUBSTRING(TABLE_NAME, 1, 14) AS UNSIGNED) > 0");
            }
            
            var whereClause = string.Join(" AND ", whereConditions);
            
            // 构建计数SQL
            var countSql = $@"
                SELECT COUNT(*) 
                FROM INFORMATION_SCHEMA.TABLES 
                WHERE {whereClause}";
            
            // 构建数据查询SQL（在数据库层面排序和分页）
            // 使用表名前14位（日期部分）进行排序，确保按时间倒序
            var offset = (pager.PageNum - 1) * pager.PageSize;
            var dataSql = $@"
                SELECT TABLE_NAME 
                FROM INFORMATION_SCHEMA.TABLES 
                WHERE {whereClause}
                ORDER BY SUBSTRING(TABLE_NAME, 1, 14) DESC
                LIMIT {pager.PageSize} OFFSET {offset}";
            
            return (countSql, dataSql);
        }

        /// <summary>
        /// 获取表查询统计信息
        /// </summary>
        /// <returns></returns>
        public object GetTableQueryStatistics()
        {
            try
            {
                // 查询所有Z_RADR_I表
                var allTablesSql = @"
                    SELECT TABLE_NAME 
                    FROM INFORMATION_SCHEMA.TABLES 
                    WHERE TABLE_TYPE = 'BASE TABLE' 
                    AND TABLE_NAME LIKE '%Z_RADR_I%'";
                
                var allTables = Context.Ado.SqlQuery<string>(allTablesSql).ToList();
                
                // 统计PPI表（不包含LR_096）
                var ppiTables = allTables.Where(table => !table.Contains("LR_096")).ToList();
                
                // 统计DBS表（包含LR_096）
                var dbsTables = allTables.Where(table => table.Contains("LR_096")).ToList();

                return new
                {
                    TotalTables = allTables.Count,
                    PPITables = ppiTables.Count,
                    DBSTables = dbsTables.Count,
                    PPITableNames = ppiTables,
                    DBSTableNames = dbsTables,
                    Message = $"总计 {allTables.Count} 个Z_RADR_I表，其中PPI表 {ppiTables.Count} 个，DBS表 {dbsTables.Count} 个"
                };
            }
            catch (Exception ex)
            {
                Console.WriteLine($"表查询统计 - 查询出错: {ex.Message}");
                throw;
            }
        }

        /// <summary>
        /// 构建查询SQL
        /// </summary>
        /// <param name="type">查询类型</param>
        /// <returns></returns>
        private string BuildQuerySql(string type)
        {
            var baseSql = @"
                SELECT TABLE_NAME 
                FROM INFORMATION_SCHEMA.TABLES 
                WHERE TABLE_TYPE = 'BASE TABLE' 
                AND TABLE_NAME LIKE '%_CDWL_%'";

            switch (type?.ToUpper())
            {
                case "PPI":
                    // PPI查询 - 所有_P_CDWL_表名（不包含LR_096和PPI_LR）
                    return baseSql + " AND TABLE_NAME NOT LIKE '%LR_096%' AND TABLE_NAME NOT LIKE '%PPI_LR%'";
                
                case "DBS":
                    // DBS查询 - 所有_P_CDWL_表名且包含有LR_096
                    return baseSql + " AND TABLE_NAME LIKE '%LR_096%'";
                
                case "PPILR":
                    // PPILR查询 - 所有_P_CDWL_表名且包含PPI_LR
                    return baseSql + " AND TABLE_NAME LIKE '%PPI_LR%'";
                
                default:
                    // 默认返回所有_P_CDWL_表
                    return baseSql;
            }
        }

        /// <summary>
        /// 从表名中提取日期时间字符串
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        private string ExtractDateTimeFromTableName(string tableName)
        {
            try
            {
                // 使用正则表达式匹配日期时间格式：YYYYMMDDHHMMSS
                // 例如：Z_RADR_I_DW001_20250728001433_P_CDWL_
                var pattern = @"(\d{14})";
                var match = Regex.Match(tableName, pattern);
                
                if (match.Success)
                {
                    return match.Groups[1].Value;
                }
                
                return string.Empty;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"提取日期时间出错 - 表名: {tableName}, 错误: {ex.Message}");
                return string.Empty;
            }
        }

        /// <summary>
        /// 格式化日期时间字符串
        /// </summary>
        /// <param name="dateTimeString">日期时间字符串（YYYYMMDDHHMMSS）</param>
        /// <returns></returns>
        private string FormatDateTime(string dateTimeString)
        {
            try
            {
                if (string.IsNullOrEmpty(dateTimeString) || dateTimeString.Length != 14)
                {
                    return dateTimeString;
                }

                // 解析日期时间：YYYYMMDDHHMMSS
                var year = dateTimeString.Substring(0, 4);
                var month = dateTimeString.Substring(4, 2);
                var day = dateTimeString.Substring(6, 2);
                var hour = dateTimeString.Substring(8, 2);
                var minute = dateTimeString.Substring(10, 2);
                var second = dateTimeString.Substring(12, 2);

                return $"{year}-{month}-{day} {hour}:{minute}:{second}";
            }
            catch (Exception ex)
            {
                Console.WriteLine($"格式化日期时间出错 - 输入: {dateTimeString}, 错误: {ex.Message}");
                return dateTimeString;
            }
        }

        /// <summary>
        /// 判断表名是否在指定的日期范围内
        /// </summary>
        /// <param name="dateTimeString">表名中的日期时间字符串（YYYYMMDDHHMMSS）</param>
        /// <param name="beginTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns></returns>
        private bool IsTableNameInDateRange(string dateTimeString, DateTime? beginTime, DateTime? endTime)
        {
            try
            {
                if (string.IsNullOrEmpty(dateTimeString) || dateTimeString.Length != 14)
                {
                    return false;
                }

                // 解析表名中的日期时间：YYYYMMDDHHMMSS
                var year = int.Parse(dateTimeString.Substring(0, 4));
                var month = int.Parse(dateTimeString.Substring(4, 2));
                var day = int.Parse(dateTimeString.Substring(6, 2));
                var hour = int.Parse(dateTimeString.Substring(8, 2));
                var minute = int.Parse(dateTimeString.Substring(10, 2));
                var second = int.Parse(dateTimeString.Substring(12, 2));

                var tableDateTime = new DateTime(year, month, day, hour, minute, second);

                // 检查是否在开始时间之后
                if (beginTime.HasValue && tableDateTime < beginTime.Value)
                {
                    return false;
                }

                // 检查是否在结束时间之前
                if (endTime.HasValue && tableDateTime > endTime.Value)
                {
                    return false;
                }

                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"判断表名日期范围出错 - 表名日期: {dateTimeString}, 错误: {ex.Message}");
                return false;
            }
        }
    }
}
